package mwa.cs.dao;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

import mwa.cs.database.Database;
import mwa.cs.model.RequestQuote;

public class RequestQuoteDAO {

	private Database db;
    /**
     * Instantiate a new object of this class
     * @param database a database
     */
    public RequestQuoteDAO(Database db) {
        this.db = db;
    }
    
 // ค้นหาข้อมูล Request quote เพื่อแสดง สำหรับยกเลิกหรือปิด เลขที่ใบคำร้อง
  	public List<Map<String, Object>> searchRequestQuote(int vaSiteId, String requestNumber) {
  		String sql = "SELECT rq.receive_code, rc.description, SUM(rq.receive_amount) AS receive_amount "
  				+ "FROM request_quote rq "
  				+ "INNER JOIN receive_code rc ON rq.receive_code = rc.receive_code "
  				+ "WHERE rq.site_id = ? AND rq.request_number = ? "
  				+ "GROUP BY rq.receive_code, rc.description "
  				+ "ORDER BY rq.receive_code";
  		List<Map<String, Object>> result = db.queryList(sql, vaSiteId, requestNumber);
  		
  		return result;
  	}
  	
 // ค้นหาข้อมูล Request quote เพื่อแสดง สำหรับยกเลิกหรือปิด เลขที่ใบคำร้อง
   	public List<Map<String, Object>> searchRequestQuoteCount(int vaSiteId, String requestNumber) {
   		String sql = "SELECT request_number FROM request_quote "
   				+ "WHERE site_id = ? AND request_number = ?";
   		List<Map<String, Object>> result = db.queryList(sql, vaSiteId, requestNumber);
   		
   		return result;
   	}
   	public void insertRequestQuote(RequestQuote voRequestQuote){
    	String sql = "INSERT INTO request_quote(site_id,request_number,receive_code,receive_amount,"
    			+ "vat_amount,total_amount,insert_user_id,insert_date,insert_time,update_user_id,"
    			+ "update_date,update_time) "
    			+ "VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
    	try {
            db.insert(sql,
            		voRequestQuote.getVnSiteId(),
            		voRequestQuote.getVaRequestNumber(),
            		voRequestQuote.getVaReceiveCode(),
            		voRequestQuote.getVnReceiveAmount(),
            		voRequestQuote.getVnVatAmount(),
            		voRequestQuote.getVnTotalAmount(),
            		voRequestQuote.getVnInsertUserId(),
            		voRequestQuote.getVaInsertDate(),
            		voRequestQuote.getVaInsertTime(),
            		voRequestQuote.getVnUpdateUserId(),
            		voRequestQuote.getVaUpdateDate(),
            		voRequestQuote.getVaUpdateTime()
            		);
        } catch (Exception e) {
        	e.printStackTrace();
        	throw new RuntimeException(e);
        }
    }
  	public void deleteRequestQuote(String vaCondition){
    	String sql = "DELETE FROM request_quote WHERE "+vaCondition;

    	try {
            db.update(sql);
        } catch (Exception e) {
        	e.printStackTrace();
        	throw new RuntimeException(e);
        }
    }
  	
  	
  	public void deleteRequestQuoteByRequestNumber(String paRequestNumber){
    	String sql = "DELETE FROM request_quote WHERE  request_number = ?";
    	try {
            db.update(sql,paRequestNumber);
        } catch (Exception e) {
        	e.printStackTrace();
        	throw new RuntimeException(e);
        }
    }
  	public List<Map<String, Object>> getRequestQuote(int vaSiteId, String requestNumber) {
  		String sql = "SELECT rq.receive_code AS RECEIVE_CODE, rc.DESCRIPTION AS RECEIVE_DESC , rq.receive_amount AS RECEIVE_AMOUNT "
  				+ "FROM request_quote rq inner join RECEIVE_CODE rc on rq.receive_code = rc.receive_code "
  				+ "WHERE rq.site_id = ? AND rq.request_number = ? "
  				+ "ORDER BY rq.receive_code";
  		List<Map<String, Object>> result = db.queryList(sql, vaSiteId, requestNumber);
  		
  		return result;
  	}
  	
 // ค้นหาข้อมูล Request quote เพื่อแสดง สำหรับรับเงินค่าอื่นๆ
   	public List<Map<String, Object>> searchRequestQuoteForReceiveOther(int vaSiteId, String requestNumber) {
   		String sql = "SELECT rc.description, SUM(receive_amount) AS receive_amount,  "
   				+ "SUM(vat_amount) AS vat_amount, SUM(total_amount) AS total_amount "
   				+ "FROM request_quote rq  "
   				+ "INNER JOIN receive_code rc ON rq.receive_code = rc.receive_code "
   				+ "WHERE rq.site_id = ? AND rq.request_number = ? "
   				+ "GROUP BY rc.description";
   		List<Map<String, Object>> result = db.queryList(sql, vaSiteId, requestNumber);
   		
   		return result;
   	}
   	
	public String getReceiveCodeByRequest(int pnSiteId,String pnRequestNumber,String pnReceiveCode) {
   		String sql = "SELECT receive_code,receive_amount FROM request_quote WHERE site_id = ? AND request_number = ? AND receive_code = ?";
   		Map<String, Object> result = db.querySingle(sql, pnSiteId, pnRequestNumber, pnReceiveCode);
        if (result != null) {
            return ((BigDecimal) result.get("RECEIVE_AMOUNT")).toString();  
        } else {
            return "0";
        }
	}
	
	public String getVAT(String pnReceiveCode , String pnDate) {
   		String sql = "select CASE WHEN r.HAVE_VAT = 1 THEN vr.RATE ELSE 0 END AS VAT "
   						+ "from RECEIVE_CODE r ,VAT_RATE vr "
   						+ "where r.RECEIVE_CODE = ? and TO_DATE(?, 'YYYYMMDD') "
   						+ "between TO_DATE(START_DATE, 'YYYYMMDD') and TO_DATE(END_DATE, 'YYYYMMDD')";
   		Map<String, Object> result = db.querySingle(sql, pnReceiveCode, pnDate);
   		
   		return ((BigDecimal) result.get("VAT")).toString();  
	}
}
